#!/bin/bash

# ods_to_dwd_init.sh all/表名 日期
# 1、判断参数是否传入
if [ $# -ne 2 ]; then
    echo "参数错误，请传入表名（all 代表所有表）和日期，格式：ods_to_dwd_init.sh all/表名 日期"
    exit 1
fi

table_name=$1
date=$2

# 定义函数来执行首日加载的 SQL
execute_initial_load() {
    local sql=$1
    /opt/module/hive/bin/hive -e "use medical;set hive.exec.dynamic.partition.mode=nonstrict;$sql"
    if [ $? -ne 0 ]; then
        echo "执行 SQL 失败: $sql"
        exit 1
    fi
}

# 根据表名匹配首日加载
case $table_name in
    "all")
        # 9.1 交易域问诊事务事实表
        sql="insert overwrite table dwd_trade_consultation_inc
    partition (dt)
select data.id,
       data.create_time                            consultation_time,
       data.consultation_fee,
       data.doctor_id,
       data.patient_id,
       data.user_id,
       date_format(data.create_time, 'yyyy-MM-dd') dt
from ods_consultation_inc
where dt = '$date'
  and type = 'bootstrap-insert';"
        execute_initial_load "$sql"

        # 9.2 交易域问诊支付成功事务事实表
        sql="insert overwrite table dwd_trade_consultation_pay_suc_inc
    partition (dt)
select data.id,
       data.update_time                            consultation_pay_suc_time,
       data.consultation_fee,
       data.doctor_id,
       data.patient_id,
       data.user_id,
       date_format(data.create_time, 'yyyy-MM-dd') dt
from ods_consultation_inc
where dt = '$date'
  and type = 'bootstrap-insert'
  and data.status <> '201'
  and data.status <> '202';"
        execute_initial_load "$sql"

        # 9.3 交易域处方开单事务事实表
        sql="insert overwrite table dwd_trade_prescription_inc
    partition (dt)
select detail.id,
       prescription_time,
       count,
       medicine_id,
       prescription_id,
       total_amount,
       consultation_id,
       doctor_id,
       patient_id,
       date_format(prescription_time, 'yyyy-MM-dd') dt
from (select data.id,
             data.create_time prescription_time,
             data.count,
             data.medicine_id,
             data.prescription_id
      from ods_prescription_detail_inc
      where dt = '$date'
        and type = 'bootstrap-insert') detail
         left join
     (select data.id,
             data.total_amount,
             data.consultation_id,
             data.doctor_id,
             data.patient_id
      from ods_prescription_inc
      where dt = '$date'
        and type = 'bootstrap-insert') info
     on detail.prescription_id = info.id;"
        execute_initial_load "$sql"

        # 9.4 交易域处方开单支付成功事务事实表
        sql="insert overwrite table dwd_trade_prescription_pay_suc_inc
    partition (dt)
select detail.id,
       prescription_pay_suc_time,
       count,
       medicine_id,
       prescription_id,
       total_amount,
       consultation_id,
       doctor_id,
       patient_id,
       date_format(prescription_pay_suc_time, 'yyyy-MM-dd') dt
from (select data.id,
             data.count,
             data.medicine_id,
             data.prescription_id
      from ods_prescription_detail_inc
      where dt = '$date'
        and type = 'bootstrap-insert') detail
         join
     (select data.id,
             data.total_amount,
             data.update_time prescription_pay_suc_time,
             data.consultation_id,
             data.doctor_id,
             data.patient_id
      from ods_prescription_inc
      where dt = '$date'
        and type = 'bootstrap-insert'
        and data.status = '203') info
     on detail.prescription_id = info.id;"
        execute_initial_load "$sql"

        # 9.5 医生域医生注册事务事实表
        sql="insert overwrite table dwd_doctor_register_inc
    partition (dt)
select doc.id,
       register_time,
       birthday,
       consultation_fee,
       gender_code,
       gender_dic.value                         gender,
       name,
       specialty_code,
       specialty_dic.value                      specialty_name,
       title_code,
       title_dic.value                          title_name,
       hospital_id,
       date_format(register_time, 'yyyy-MM-dd') dt
from (select data.id,
             data.create_time                                                                register_time,
             data.birthday,
             data.consultation_fee,
             data.gender                                                                     gender_code,
             concat(substr(data.name, 1, 1), regexp_replace(substr(data.name, 2), '.', '*')) name,
             data.specialty                                                                  specialty_code,
             data.title                                                                      title_code,
             data.hospital_id
      from ods_doctor_inc
      where dt = '$date'
        and type = 'bootstrap-insert') doc
         left join (
    select id,
           value
    from ods_dict_full
    where dt = '$date'
) gender_dic
                   on doc.gender_code = gender_dic.id
         left join (
    select id,
           value
    from ods_dict_full
    where dt = '$date'
) specialty_dic
                   on doc.specialty_code = specialty_dic.id
         left join (
    select id,
           value
    from ods_dict_full
    where dt = '$date'
) title_dic
                   on doc.title_code = title_dic.id;"
        execute_initial_load "$sql"

        # 9.6 用户域用户注册事务事实表
        sql="insert overwrite table dwd_user_register_inc
    partition (dt)
select data.id,
       data.create_time                                    register_time,
       concat('*@', split(data.email, '@')[1])             email,
       if(data.telephone regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',
          concat(substr(data.telephone, 1, 3), '*'), null) telephone,
       data.username,
       date_format(data.create_time, 'yyyy-MM-dd')         dt
from ods_user_inc
where dt = '$date'
  and type = 'bootstrap-insert';"
        execute_initial_load "$sql"

        # 9.7 用户域患者登记事务事实表
        sql="insert overwrite table dwd_user_patient_add_inc
    partition (dt)
select patient.id,
       add_time,
       birthday,
       gender_code,
       dic.value                           gender,
       name,
       user_id,
       date_format(add_time, 'yyyy-MM-dd') dt
from (select data.id,
             data.create_time add_time,
             data.birthday,
             data.gender      gender_code,
             data.name,
             data.user_id
      from ods_patient_inc
      where dt = '$date'
        and type = 'bootstrap-insert') patient
         left join (select id,
                           value
                    from ods_dict_full
                    where dt = '$date') dic
                   on patient.gender_code = dic.id;"
        execute_initial_load "$sql"

        # 9.8 互动域用户评价事务事实表
        sql="insert overwrite table dwd_interaction_review_inc
    partition (dt)
select data.id,
       data.update_time                            review_time,
       data.rating,
       data.doctor_id,
       data.patient_id,
       data.user_id,
       date_format(data.update_time, 'yyyy-MM-dd') dt
from ods_consultation_inc
where dt = '$date'
  and type = 'bootstrap-insert'
  and data.status = '207';"
        execute_initial_load "$sql"
        ;;
    "dwd_trade_consultation_inc")
        sql="insert overwrite table dwd_trade_consultation_inc
    partition (dt)
select data.id,
       data.create_time                            consultation_time,
       data.consultation_fee,
       data.doctor_id,
       data.patient_id,
       data.user_id,
       date_format(data.create_time, 'yyyy-MM-dd') dt
from ods_consultation_inc
where dt = '$date'
  and type = 'bootstrap-insert';"
        execute_initial_load "$sql"
        ;;
    "dwd_trade_consultation_pay_suc_inc")
        sql="insert overwrite table dwd_trade_consultation_pay_suc_inc
    partition (dt)
select data.id,
       data.update_time                            consultation_pay_suc_time,
       data.consultation_fee,
       data.doctor_id,
       data.patient_id,
       data.user_id,
       date_format(data.create_time, 'yyyy-MM-dd') dt
from ods_consultation_inc
where dt = '$date'
  and type = 'bootstrap-insert'
  and data.status <> '201'
  and data.status <> '202';"
        execute_initial_load "$sql"
        ;;
    "dwd_trade_prescription_inc")
        sql="insert overwrite table dwd_trade_prescription_inc
    partition (dt)
select detail.id,
       prescription_time,
       count,
       medicine_id,
       prescription_id,
       total_amount,
       consultation_id,
       doctor_id,
       patient_id,
       date_format(prescription_time, 'yyyy-MM-dd') dt
from (select data.id,
             data.create_time prescription_time,
             data.count,
             data.medicine_id,
             data.prescription_id
      from ods_prescription_detail_inc
      where dt = '$date'
        and type = 'bootstrap-insert') detail
         left join
     (select data.id,
             data.total_amount,
             data.consultation_id,
             data.doctor_id,
             data.patient_id
      from ods_prescription_inc
      where dt = '$date'
        and type = 'bootstrap-insert') info
     on detail.prescription_id = info.id;"
        execute_initial_load "$sql"
        ;;
    "dwd_trade_prescription_pay_suc_inc")
        sql="insert overwrite table dwd_trade_prescription_pay_suc_inc
    partition (dt)
select detail.id,
       prescription_pay_suc_time,
       count,
       medicine_id,
       prescription_id,
       total_amount,
       consultation_id,
       doctor_id,
       patient_id,
       date_format(prescription_pay_suc_time, 'yyyy-MM-dd') dt
from (select data.id,
             data.count,
             data.medicine_id,
             data.prescription_id
      from ods_prescription_detail_inc
      where dt = '$date'
        and type = 'bootstrap-insert') detail
         join
     (select data.id,
             data.total_amount,
             data.update_time prescription_pay_suc_time,
             data.consultation_id,
             data.doctor_id,
             data.patient_id
      from ods_prescription_inc
      where dt = '$date'
        and type = 'bootstrap-insert'
        and data.status = '203') info
     on detail.prescription_id = info.id;"
        execute_initial_load "$sql"
        ;;
    "dwd_doctor_register_inc")
        sql="insert overwrite table dwd_doctor_register_inc
    partition (dt)
select doc.id,
       register_time,
       birthday,
       consultation_fee,
       gender_code,
       gender_dic.value                         gender,
       name,
       specialty_code,
       specialty_dic.value                      specialty_name,
       title_code,
       title_dic.value                          title_name,
       hospital_id,
       date_format(register_time, 'yyyy-MM-dd') dt
from (select data.id,
             data.create_time                                                                register_time,
             data.birthday,
             data.consultation_fee,
             data.gender                                                                     gender_code,
             concat(substr(data.name, 1, 1), regexp_replace(substr(data.name, 2), '.', '*')) name,
             data.specialty                                                                  specialty_code,
             data.title                                                                      title_code,
             data.hospital_id
      from ods_doctor_inc
      where dt = '$date'
        and type = 'bootstrap-insert') doc
         left join (
    select id,
           value
    from ods_dict_full
    where dt = '$date'
) gender_dic
                   on doc.gender_code = gender_dic.id
         left join (
    select id,
           value
    from ods_dict_full
    where dt = '$date'
) specialty_dic
                   on doc.specialty_code = specialty_dic.id
         left join (
    select id,
           value
    from ods_dict_full
    where dt = '$date'
) title_dic
                   on doc.title_code = title_dic.id;"
        execute_initial_load "$sql"
        ;;
    "dwd_user_register_inc")
        sql="insert overwrite table dwd_user_register_inc
    partition (dt)
select data.id,
       data.create_time                                    register_time,
       concat('*@', split(data.email, '@')[1])             email,
       if(data.telephone regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',
          concat(substr(data.telephone, 1, 3), '*'), null) telephone,
       data.username,
       date_format(data.create_time, 'yyyy-MM-dd')         dt
from ods_user_inc
where dt = '$date'
  and type = 'bootstrap-insert';"
        execute_initial_load "$sql"
        ;;
    "dwd_user_patient_add_inc")
        sql="insert overwrite table dwd_user_patient_add_inc
    partition (dt)
select patient.id,
       add_time,
       birthday,
       gender_code,
       dic.value                           gender,
       name,
       user_id,
       date_format(add_time, 'yyyy-MM-dd') dt
from (select data.id,
             data.create_time add_time,
             data.birthday,
             data.gender      gender_code,
             data.name,
             data.user_id
      from ods_patient_inc
      where dt = '$date'
        and type = 'bootstrap-insert') patient
         left join (select id,
                           value
                    from ods_dict_full
                    where dt = '$date') dic
                   on patient.gender_code = dic.id;"
        execute_initial_load "$sql"
        ;;
    "dwd_interaction_review_inc")
        sql="insert overwrite table dwd_interaction_review_inc
    partition (dt)
select data.id,
       data.update_time                            review_time,
       data.rating,
       data.doctor_id,
       data.patient_id,
       data.user_id,
       date_format(data.update_time, 'yyyy-MM-dd') dt
from ods_consultation_inc
where dt = '$date'
  and type = 'bootstrap-insert'
  and data.status = '207';"
        execute_initial_load "$sql"
        ;;
    *)
        echo "不支持的表名: $table_name"
        exit 1
        ;;
esac

echo "首日加载完成"